<?php
namespace app\admin\controller;
use controller\BasicAdmin;
use think\Db;
use think\ExcelToArrary;
use PHPExcel_IOFactory;
use PHPExcel_Reader_Excel2007;
use PHPExcel_Reader_Excel5;
use PHPExcel_Reader_CSV;
class Adminmember extends BasicAdmin{
    public $table ='bonus_member';
    public $realbonusmember = 'real_bonus_member';
    public function index(){
        list($get, $db) = [$this->request->get(), Db::name($this->table)->alias('a')->join('t_area b','a.area_id=b.ID','LEFT')];
        $where = '1=1';
        isset($get['tel'])&&$get['tel']!='' ? $where ='a.tel ='.trim($get['tel'])  : '';
        isset($get['area'])&&$get['area']!='' ? $db->where(['a.area_id'=>$get['area']]) :'';
        isset($get['status'])&&$get['status']!='' ? $db->where(['a.status'=>$get['status']]) :'';
        $list = parent::_list($db->where($where)->order('a.created_at DESC'),1,'','');
        $list['area'] =Db::name('area')->where(['LevelType'=>1])->field('ID,Name')->select();
        $list['status'] = [1=>'正常',2=>'禁用'];
        return $this->fetch('', ['title' => '当前分红内部列表','list'=>$list]);
    }

    public function edit(){
        list($get, $db) = [$this->request->get(), Db::name($this->table)->alias('a')->join('t_area b','a.area_id=b.ID','LEFT')];
        $info = $db->field('a.id,a.status,a.name,a.tel,a.proportion')->where(['a.id'=>$get['id']])->find();
        return $this->fetch('', ['title' => '当前分红内部编辑','info'=>$info]);
    }
    public function postedit(){
        $post = $this->request->post();
        $data['proportion'] = round($post['proportion'],2);
        $data['status'] = $post['status'];
        $result = Db::name($this->table)->where(['id'=>$post['id']])->update($data);
        if(!empty($result)){
            $this->success('修改成功','');
        }else{
            $this->error('没有数据发生改变','');
        }
    }

    /**
     * 所有
     */
    public function allList(){
        $get = $this->request->get();
        isset($get['tel']) && $get['tel'] !='' ?$where['tel'] = trim($get['tel']) : $where = '1=1';
        $list = Db::name($this->realbonusmember)
            ->alias('a')
            ->join('t_area b','a.area_id=b.ID','LEFT')
            ->where($where)
            ->select();
        return $this->fetch('', ['title' => '所有内部列表','list'=>$list]);
    }
    public function editall(){
        $get = $this->request->get();
        $info = Db::name($this->realbonusmember)
            ->alias('a')
            ->join('t_area b','a.area_id=b.ID','LEFT')
            ->where(['a.id'=>$get['id']])
            ->find();
        return $this->fetch('', ['title' => '所有内部编辑','info'=>$info]);
    }

    /**
     * 提交
     */
    public function editallpost(){
        $post = $this->request->post();
        $data['name'] = $post['name'];
        $data['proportion'] = $post['proportion'];
        $data['status'] = $post['status'];
        $result = Db::name($this->realbonusmember)->where(['id'=>$post['id']])->update($data);
        if(!empty($result)){
            $this->success('修改成功','');
        }else{
            $this->error('没有数据发生改变','');
        }
    }
    /**
     * 导入execl
     */
    public function addexecl(){
        return $this->fetch('form');
    }

    public function postexcel(){

        require_once APP_PATH."common/PHPExcel/PHPExcel/PHPExcel.php";
        require_once APP_PATH."common/PHPExcel/PHPExcel/PHPExcel/IOFactory.php";
        require_once APP_PATH."common/PHPExcel/PHPExcel/PHPExcel/Reader/Excel5.php";
        $file = request()->file('file');
        if($file){
            $info = $file->move('./static/excel');
            $filename = $file->getInfo()["name"];
            $names = explode('.',$filename);
            $extension = $names[1];
            $path = 'static/excel/'.$info->getSaveName();
            $objReader=\PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format
            if ($extension =='xlsx') {
                require_once APP_PATH.'common/PHPExcel/PHPExcel/PHPExcel/Reader/Excel2007.php';
                $objReader = new PHPExcel_Reader_Excel2007();
                $objPHPExcel = $objReader ->load($path);
            } else if ($extension =='xls') {
                $objReader = new PHPExcel_Reader_Excel5();
                $objPHPExcel = $objReader ->load($file);
            } else if ($extension=='csv') {
                $PHPReader = new PHPExcel_Reader_CSV();
                //默认输入字符集
                $PHPReader->setInputEncoding('GBK');
                //默认的分隔符
                $PHPReader->setDelimiter(',');
                //载入文件
                $objPHPExcel = $PHPReader->load($file);
            }else{
                echo json_encode(['res'=>201,'msg'=>"只能上传excelw文件"]);die;
            }
            $sheet = $objPHPExcel->getSheet(0);//获取第一个工作表
            $highestRow=$sheet->getHighestRow();//取得总行数
            $highestColumn=$sheet->getHighestColumn(); //取得总列数
            $isusernum  = Db::name('config')->where(['name'=>'bonus_proportion'])->value('data');
            $data = array();
            $isnum = 0;
            $a = 0;
            //循环读取excel文件,读取一条,插入一条
            for($j=2;$j<=$highestRow;$j++){
                $a++;
                //从第一行开始读取数据
                $str='';
                for($k='A';$k<=$highestColumn;$k++){ //从A列读取数据
                //这种方法简单，但有不妥，以'\\'合并为数组，再分割\为字段值插入到数据库,实测在excel中，如果某单元格的值包含了\\导入的数据会为空
                    $str.=$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue().'\\';//读取单元格
                }

                //explode:函数把字符串分割为数组。
                $strs=explode("\\",$str);
                $isnum += $strs[4];
                if(!empty($strs[3])){//必须要有手机号码才可以导入
                    $d["username"] = $strs[1];
                    $d["name"] = $strs[2];
                    $d["tel"] = $strs[3];
                    $d["proportion"] = $strs[4];
                    $d["area_id"] = self::getarea($strs[5]);
                    $d["status"] = 1;
                    $d["created_at"] = date('Y-m-d H:i:s',time());
                    $d["updated_at"] = date('Y-m-d H:i:s',time());
                    array_push($data,$d);
                }
                if($isnum >$isusernum){
                    echo json_encode(['res'=>201,'msg'=>"已超过内部员工分红总和 ,请重新修改内部员工分红比列.(第 $a 行，$strs[2])"]);die;
                }

            }

            Db::name('bonus_member')->where('id<>0')->delete();

            $res = Db::name("bonus_member")->insertAll($data);
            //添加纪录
            $insertlog['add_time'] = time();
            $insertlog['name'] = $filename;
            $insertlog['new_name'] = $info->getSaveName();
            $insertlog['download_url'] = $path;
            Db::name('admin_excel_log')->insert($insertlog);
            if($res){
                echo json_encode(['code'=>200,'msg'=>'上传成功']);
            }else{
                echo json_encode(['code'=>201,'msg'=>'上传失败']);
            }
        }
    }

    /**
     * @param string $name
     * @return mixed|void
     * 获取地址id
     */
    protected function getarea($name=''){
        if(!$name)return;
        return Db::name('area')->where(['Name'=>$name])->value('ID');
    }
    public function getExcelLog(){

        $list = parent::_list(Db::name('admin_excel_log'),1,'','');
        foreach ($list['list'] as $k=>$v){
           $list['list'][$k]['download_url'] = 'http://admin.zgzdtx.com/'.$v['download_url'];
        }
        return $this->fetch('log', ['title' => 'excel导入记录','list'=>$list]);
    }


}